Formula Script Editor

Pyramid’s Formulate tool set enables non-technical users to build advanced business logic calculations, lists, KPIs, parameters, and scripts using a point-and-click interface. The code editor extends this functionality with an “IDE” for advanced users to write both PQL and MDX statements, complete with drag-and-drop function libraries and metadata models.

The IDE is accessible from the Custom List and Custom Member designers. To review an example of how to build a custom member in the IDE, click here. For an example of creating custom lists in the IDE, click here.

The script editor allows advanced users to construct PQL and MDX formulations using code, rather than the nodes used in the main Custom List and Custom Member designers.

Script Editor Workspace in Formulate

From the Script Editor, users navigate the Functions listing (red arrow below) and drag the require function onto the canvas (orange highlight).

From the Metadata panel (blue highlight), the user then injects the required model metadata into the function.

As usual, the execute button (green arrow) is used to test the function; the results are displayed in the Result Preview panel (purple highlight).

Access the Script Editor

To access the IDE, click the Script Mode button from the top ribbon in the Custom List or Custom Member designer.

Note that:

  • Any logic already added to the canvas will appear in script mode
  • Once in script mode, you cannot go back to the canvas view
  • You can add scripts from other locations; for example, copy and paste scripts, or drop files containing scripts, or drop metadata from Microsoft SQL Server Management Studio onto the script window
  • Use the Metadata panel to insert the relevant metadata into the script

Script Language

The script language is PQL, unless using OLAP, Tabular or BW models, in which case the script language is MDX. To review a reference library of MDX functions, see Microsoft's MDX Function Reference.